<?php

namespace App\Http\Controllers\Api\V1;

use App\Models\Child;
use App\Models\File;
use App\Models\History;
use App\Http\Controllers\Controller;
use App\Models\Snapshot;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Symfony\Component\Process\Process;
use Illuminate\Support\Facades\DB;


class DataScreenController extends Controller
{

    private $key = 'zhongjiao';

    /**
     * 训练合计数量
     *
     * @param Request $request
     * @return mixed
     */
    public function historyCount(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $historyCount = History::count();

        return response()->json(['status' => 200, 'data' => ['result' => $historyCount]]);
    }

    /**
     * 训练最多的前15个项目
     *
     * @param Request $request
     * @return mixed
     */

    public function top15(Request $request)
    {

        $data = $request->only(['last_id', 'token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $historyCount = DB::select("SELECT num, b.name FROM ( SELECT COUNT(id) as num, game_id FROM histories GROUP by game_id ORDER by num DESC ) as temp LEFT JOIN games b ON temp.game_id = b.id LIMIT 15");

        return response()->json(['status' => 200, 'data' => ['result' => $historyCount]]);
    }

    /**
     * 机构学生数量
     *
     * @param Request $request
     * @return mixed
     */

    public function agencyStudentNum(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $agencyStudent = DB::select("SELECT num, b.name FROM ( SELECT COUNT(id) as num, agency_id FROM children GROUP by agency_id ORDER by num DESC ) as temp LEFT JOIN agencies b ON temp.agency_id = b.id LIMIT 15");

        return response()->json(['status' => 200, 'data' => ['result' => $agencyStudent]]);
    }

    /**
     * 按省份统计学生数量
     *
     * @param Request $request
     * @return mixed
     */

    public function studentNum(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $agencyStudent = DB::select("SELECT num, b.name,b.province FROM ( SELECT COUNT(id) as num, agency_id FROM children GROUP by agency_id ORDER by num DESC ) as temp LEFT JOIN agencies b ON temp.agency_id = b.id");

        $result = [];
        foreach ($agencyStudent as $item) {
            if (isset($result[$item->province])) {
                $result[$item->province] += $item->num;
            } else {
                $result[$item->province] = $item->num;
            }
        }

        return response()->json(['status' => 200, 'data' => ['result' => $result]]);
    }

    /**
     * 按训练方向分布
     *
     * @param Request $request
     * @return mixed
     */

    public function categoryNum(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $sql = 'select count( case when a.category_id = 1 then 1 else null end ) as "紧张感", count( case when a.category_id = 2 then 1 else null end ) as "稳定性", count( case when a.category_id = 3 then 1 else null end ) as "广度", count( case when a.category_id = 4 then 1 else null end ) as "转移", count( case when a.category_id = 5 then 1 else null end ) as "分配", b.name as grade_name from histories a left join grades b on a.grade_id = b.id group by a.grade_id';

        $categoryNum = DB::select($sql);

        return response()->json(['status' => 200, 'data' => ['result' => $categoryNum]]);
    }

    /**
     * 按训练项目统计
     *
     * @param Request $request
     * @return mixed
     */

    public function historyNum(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $sql = 'select a.game_id, count(a.id) as num, a.result, b.name as game_name from histories a left join games b on a.game_id = b.id group by a.game_id, a.result order by game_id';

        $categoryNum = DB::select($sql);

        $result = [];

        if (!empty($categoryNum)) {
            foreach ($categoryNum as $item) {
                if (!isset($result[$item->game_id])) {
                    $temp = ['game_name'=>$item->game_name, 'game_id'=>$item->game_id];
                    $result[$item->game_id] = $temp;
                }

                if ($item->result == 1) {
                    $result[$item->game_id]['num1'] = $item->num;
                } else {
                    $result[$item->game_id]['num2'] = $item->num;
                }
            }
        }


        return response()->json(['status' => 200, 'data' => ['result' => array_values($result)]]);
    }

    /**
     * 行为动态追踪统计
     *
     * @param Request $request
     * @return mixed
     */

    public function behavior(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key) {
            return response()->json(['status' => 300, 'msg' => '数据为空']);
        }

        $sql = 'select name, ROUND(AVG(score)) as score, test_date from behaviors group by name,test_date order by test_date';

        $categoryNum = DB::select($sql);
        $result = [];
        if (!empty($categoryNum)) {
            foreach ($categoryNum as $item) {
                if (!isset($result[$item->test_date])) {
                    $result[$item->test_date] = ['test_date'=>$item->test_date];
                }

                $result[$item->test_date][$item->name] = $item->score;
            }
        }

        return response()->json(['status' => 200, 'data' => ['result' => array_values($result)]]);
    }
}
